<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  
  <title>Hexo</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="第十四章JDBC01 JDBC快速入门  概念：  Java DataBase Connectivity  Java 数据库连接， Java语言操作数据库      JDBC本质：  其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。 Person">
<meta property="og:type" content="article">
<meta property="og:title" content="Hexo">
<meta property="og:url" content="https://goofyer.gitee.io/notes-on-computer-expertise/2021/12/20/Java/%E7%AC%AC%E5%8D%81%E5%9B%9B%E7%AB%A0JDBC/index.html">
<meta property="og:site_name" content="Hexo">
<meta property="og:description" content="第十四章JDBC01 JDBC快速入门  概念：  Java DataBase Connectivity  Java 数据库连接， Java语言操作数据库      JDBC本质：  其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。 Person">
<meta property="og:locale" content="en_US">
<meta property="article:published_time" content="2021-12-19T17:33:29.544Z">
<meta property="article:modified_time" content="2021-12-19T17:33:30.744Z">
<meta property="article:author" content="John Doe">
<meta name="twitter:card" content="summary">
  
    <link rel="alternate" href="/notes-on-computer-expertise/atom.xml" title="Hexo" type="application/atom+xml">
  
  
    <link rel="shortcut icon" href="/notes-on-computer-expertise/favicon.png">
  
  
    
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/typeface-source-code-pro@0.0.71/index.min.css">

  
  
<link rel="stylesheet" href="/notes-on-computer-expertise/css/style.css">

  
    
<link rel="stylesheet" href="/notes-on-computer-expertise/fancybox/jquery.fancybox.min.css">

  
<meta name="generator" content="Hexo 5.4.0"></head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/notes-on-computer-expertise/" id="logo">Hexo</a>
      </h1>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/notes-on-computer-expertise/">Home</a>
        
          <a class="main-nav-link" href="/notes-on-computer-expertise/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/notes-on-computer-expertise/atom.xml" title="RSS Feed"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="Search"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="https://goofyer.gitee.io/notes-on-computer-expertise"></form>
      </div>
    </div>
  </div>
</header>

      <div class="outer">
        <section id="main"><article id="post-Java/第十四章JDBC" class="h-entry article article-type-post" itemprop="blogPost" itemscope itemtype="https://schema.org/BlogPosting">
  <div class="article-meta">
    <a href="/notes-on-computer-expertise/2021/12/20/Java/%E7%AC%AC%E5%8D%81%E5%9B%9B%E7%AB%A0JDBC/" class="article-date">
  <time class="dt-published" datetime="2021-12-19T17:33:29.544Z" itemprop="datePublished">2021-12-20</time>
</a>
    
  </div>
  <div class="article-inner">
    
    
    <div class="e-content article-entry" itemprop="articleBody">
      
        <h2 id="第十四章JDBC"><a href="#第十四章JDBC" class="headerlink" title="第十四章JDBC"></a>第十四章JDBC</h2><h3 id="01-JDBC快速入门"><a href="#01-JDBC快速入门" class="headerlink" title="01 JDBC快速入门"></a>01 JDBC快速入门</h3><ul>
<li><ol>
<li><p>概念：</p>
<ul>
<li>Java DataBase Connectivity  Java 数据库连接， Java语言操作数据库</li>
</ul>
</li>
</ol>
</li>
<li><ul>
<li><p>JDBC本质：</p>
<ul>
<li>其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。</li>
<li>Person 接口：eat()方法   Worker实现类      Person p = new Worker(); // 父类引用指向子类对象</li>
<li>p.eat(); // 会调用Worker()实现类的eat方法，产生多态调用</li>
<li>JDBC编程，以接口类型调用方法。导入驱动jar包后，真正执行的是驱动jar包实现类对应的实现方法。</li>
</ul>
</li>
</ul>
</li>
</ul>
<h3 id="2-快速入门："><a href="#2-快速入门：" class="headerlink" title="2. 快速入门："></a>2. 快速入门：</h3><ul>
<li><ul>
<li><p>步骤：</p>
<ul>
<li><ol>
<li><p>导入驱动jar包 mysql-connector-java-5.1.37-bin.jar</p>
<ul>
<li>1.复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下</li>
<li>2.右键—&gt;Add As Library</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li>注册驱动</li>
</ol>
</li>
<li><ol>
<li>获取数据库连接对象 Connection</li>
</ol>
</li>
<li><ol>
<li>定义sql</li>
</ol>
</li>
<li><ol>
<li>获取执行sql语句的对象 Statement</li>
</ol>
</li>
<li><ol>
<li>执行sql，接受返回结果</li>
</ol>
</li>
<li><ol>
<li>处理结果</li>
</ol>
</li>
<li><ol>
<li>释放资源</li>
</ol>
</li>
</ul>
</li>
</ul>
</li>
<li><ul>
<li><p>代码实现：</p>
<ul>
<li>/**</li>
</ul>
</li>
<li>JDBC快速入门<br>*/<br>public class JdbcDemo1 {<br>public static void main(String[] args) throws Exception {<pre><code>//1.导入驱动jar包
//2.注册驱动
Class.forName(&quot;com.mysql.jdbc.Driver&quot;); // 把类通过Class.forName()加载进内存
//3.获取数据库连接对象
Connection conn = DriverManager.getConnection(&quot;jdbc:mysql://localhost:3306/db3&quot;, &quot;root&quot;, &quot;root&quot;);
//4.定义sql语句
String sql = &quot;update account set balance = 2000 where id = 1&quot;;
//5.获取执行sql的对象 Statement
Statement stmt = conn.createStatement();
//6.执行sql
int count = stmt.executeUpdate(sql);
//7.处理结果
System.out.println(count);
//8.释放资源
stmt.close();
conn.close();
</code></pre>}<br>}</li>
</ul>
</li>
</ul>
<h3 id="3-详解各个对象："><a href="#3-详解各个对象：" class="headerlink" title="3. 详解各个对象："></a>3. 详解各个对象：</h3><ul>
<li><ol>
<li><p>DriverManager：驱动管理对象</p>
<ul>
<li><ul>
<li><p>功能：</p>
<ul>
<li><ol>
<li><p>注册驱动：告诉程序该使用哪一个数据库驱动jar包</p>
<ul>
<li>static void registerDriver(Driver driver) :注册与给定的驱动程序 DriverManager 。 </li>
<li><p>写代码使用：</p>
<ul>
<li>Class.forName(“com.mysql.jdbc.Driver”);</li>
</ul>
</li>
<li><p>通过查看源码发现：</p>
<ul>
<li><p>在com.mysql.jdbc.Driver类中存在静态代码块</p>
<ul>
<li>static {<br>try {<br>java.sql.DriverManager.registerDriver(new Driver());<br>} catch (SQLException E) {<br>throw new RuntimeException(“Can’t register driver!”);<br>}<br>}</li>
</ul>
</li>
</ul>
</li>
<li><p>注意：mysql5之后的驱动jar包可以省略注册驱动的步骤。</p>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>获取数据库连接</p>
<ul>
<li><ul>
<li>方法：static Connection getConnection(String url, String user, String password) </li>
</ul>
</li>
<li><ul>
<li>参数：</li>
</ul>
</li>
<li><ul>
<li>url：指定连接的路径</li>
</ul>
</li>
<li><ul>
<li>语法：jdbc:mysql://ip地址(域名):端口号/数据库名称</li>
</ul>
</li>
<li><ul>
<li>例子：jdbc:mysql://localhost:3306/db3</li>
</ul>
</li>
<li><ul>
<li>细节：如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，则url可以简写为：jdbc:mysql:///数据库名称</li>
</ul>
</li>
<li><ul>
<li>user：用户名</li>
</ul>
</li>
<li><ul>
<li>password：密码</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>Connection：数据库连接对象</p>
<ul>
<li><ol>
<li><p>功能：</p>
<ul>
<li><ol>
<li>获取执行sql 的对象</li>
</ol>
<ul>
<li><ul>
<li>Statement createStatement()</li>
</ul>
</li>
<li><ul>
<li>PreparedStatement prepareStatement(String sql)  </li>
</ul>
</li>
</ul>
</li>
<li><ol>
<li>管理事务：</li>
</ol>
<ul>
<li><ul>
<li>开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务</li>
</ul>
</li>
<li><ul>
<li>提交事务：commit() </li>
</ul>
</li>
<li><ul>
<li>回滚事务：rollback() </li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>Statement：执行sql的对象</p>
<ul>
<li><ol>
<li><p>执行sql</p>
<ul>
<li><ol>
<li>boolean execute(String sql) ：可以执行任意的sql（了解）</li>
</ol>
</li>
<li><ol>
<li>int executeUpdate(String sql) ：执行DML(insert、update、delete)语句、DDL(create、alter、drop)语句</li>
</ol>
<ul>
<li><ul>
<li>返回值：影响的行数，可以通过这个影响的行数判断DML语句是否执行成功。返回值&gt;0，则执行成功；反之，则失败。</li>
</ul>
</li>
</ul>
</li>
<li><ol>
<li>ResultSet executeQuery(String sql) ：执行DQL（select）语句</li>
</ol>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li>练习：</li>
</ol>
</li>
<li><ol>
<li>account表 添加一条记录</li>
</ol>
</li>
<li><ol>
<li>account表 修改记录</li>
</ol>
</li>
<li><ol>
<li>account表 删除一条记录</li>
</ol>
</li>
<li><p>代码：package cn.itcast.jdbc;</p>
<ul>
<li>/**</li>
</ul>
</li>
</ul>
</li>
</ol>
<ul>
<li>account表 添加一条记录 insert 语句<br>*/<br>public class JDBCDemo2 {<br>public static void main(String[] args) {<pre><code>Statement stmt = null;
Connection conn = null;
try &#123;
    //1. 注册驱动
    Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
    //2. 定义sql
    String sql = &quot;insert into account values(null,&#39;王五&#39;,3000)&quot;;
    //3.获取Connection对象
    conn = DriverManager.getConnection(&quot;jdbc:mysql:///db3&quot;, &quot;root&quot;, &quot;root&quot;);
    //4.获取执行sql的对象 Statement
    stmt = conn.createStatement();
    //5.执行sql
    int count = stmt.executeUpdate(sql);//影响的行数
    //6.处理结果
    System.out.println(count);
    if (count &gt; 0) &#123;
        System.out.println(&quot;添加成功！&quot;);
    &#125; else &#123;
        System.out.println(&quot;添加失败！&quot;);
    &#125;
&#125; catch (ClassNotFoundException e) &#123;
    e.printStackTrace();
&#125; catch (SQLException e) &#123;
    e.printStackTrace();
&#125; finally &#123;
    //stmt.close();
    //7. 释放资源
    //避免空指针异常
    if (stmt != null) &#123;
        try &#123;
            stmt.close();
        &#125; catch (SQLException e) &#123;
            e.printStackTrace();
        &#125;
    &#125;
    if (conn != null) &#123;
        try &#123;
            conn.close();
        &#125; catch (SQLException e) &#123;
            e.printStackTrace();
        &#125;
    &#125;
&#125;
</code></pre>}<br>}<br>JDBC练习_update语句<br>account表 修改记录</li>
</ul>
</li>
</ul>
<p>package cn.itcast.jdbc;</p>
<p>import java.sql.Connection;<br>import java.sql.DriverManager;<br>import java.sql.SQLException;<br>import java.sql.Statement;</p>
<p>/**</p>
<ul>
<li>account表 修改记录<br>*/<br>public class JDBCDemo3 {<br> public static void main(String[] args) {<pre><code> Connection conn = null;
 Statement stmt = null;
 try &#123;
     //1.注册驱动
     Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
     //2.获取连接对象
     conn = DriverManager.getConnection(&quot;jdbc:mysql:///db3&quot;, &quot;root&quot;, &quot;root&quot;);
     //3.定义sql
     String sql = &quot;update account set balance = 1500 where id = 3&quot;;
     //4.获取执行sql对象
     stmt = conn.createStatement();
     //5.执行sql
     int count = stmt.executeUpdate(sql);
     //6.处理结果
     System.out.println(count);
     if (count &gt; 0) &#123;
         System.out.println(&quot;修改成功！&quot;);
     &#125; else &#123;
         System.out.println(&quot;修改失败！&quot;);
     &#125;
 &#125; catch (ClassNotFoundException e) &#123;
     e.printStackTrace();
 &#125; catch (SQLException e) &#123;
     e.printStackTrace();
 &#125; finally &#123;
     //7.释放资源
     if (stmt != null) &#123;
         try &#123;
             stmt.close();
         &#125; catch (SQLException e) &#123;
             e.printStackTrace();
         &#125;
     &#125;
     if (conn != null) &#123;
         try &#123;
             conn.close();
         &#125; catch (SQLException e) &#123;
             e.printStackTrace();
         &#125;
     &#125;
 &#125;
</code></pre> }<br>}<br>JDBC练习_update、DDL语句<br>account表 删除一条记录</li>
</ul>
<p>package cn.itcast.jdbc;</p>
<p>import cn.itcast.util.JDBCUtils;</p>
<p>import java.sql.Connection;<br>import java.sql.DriverManager;<br>import java.sql.SQLException;<br>import java.sql.Statement;</p>
<p>/**</p>
<ul>
<li>account表 删除一条记录<br>*/<br>public class JDBCDemo4 {<br> public static void main(String[] args) {<pre><code> Connection conn = null;
 Statement stmt = null;
 try &#123;
     //1. 注册驱动
     Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
     //2.获取连接对象
     conn = DriverManager.getConnection(&quot;jdbc:mysql:///db3&quot;, &quot;root&quot;, &quot;root&quot;);
     //conn = JDBCUtils.getConnection(&quot;jdbc:mysql:///db3&quot;, &quot;root&quot;, &quot;root&quot;);
     //3.定义sql
     String sql = &quot;delete from account where id = 3&quot;;
     //4.获取执行sql对象
     stmt = conn.createStatement();
     //5.执行sql
     int count = stmt.executeUpdate(sql);
     //6.处理结果
     System.out.println(count);
     if (count &gt; 0) &#123;
         System.out.println(&quot;删除成功！&quot;);
     &#125; else &#123;
         System.out.println(&quot;删除失败！&quot;);
     &#125;
 &#125; catch (ClassNotFoundException e) &#123;
     e.printStackTrace();
 &#125; catch (SQLException e) &#123;
     e.printStackTrace();
 &#125; finally &#123;
     //7.释放资源
     if (stmt != null) &#123;
         try &#123;
             stmt.close();
         &#125; catch (SQLException e) &#123;
             e.printStackTrace();
         &#125;
     &#125;
     if (conn != null) &#123;
         try &#123;
             conn.close();
         &#125; catch (SQLException e) &#123;
             e.printStackTrace();
         &#125;
     &#125;
 &#125;
</code></pre> }<br>}<br>执行DDL语句</li>
</ul>
<p>package cn.itcast.jdbc;</p>
<p>import java.sql.Connection;<br>import java.sql.DriverManager;<br>import java.sql.SQLException;<br>import java.sql.Statement;</p>
<p>/**</p>
<ul>
<li>执行DDL语句<br>*/<br>public class JDBCDemo5 {<br> public static void main(String[] args) {<pre><code> Connection conn = null;
 Statement stmt = null;
 try &#123;
     //1. 注册驱动
     Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
     //2.获取连接对象
     conn = DriverManager.getConnection(&quot;jdbc:mysql:///db3&quot;, &quot;root&quot;, &quot;root&quot;);
     //3.定义sql
     String sql = &quot;create table student (id int, name varchar(20))&quot;;
     //4.获取执行sql对象
     stmt = conn.createStatement();
     //5.执行sql
     int count = stmt.executeUpdate(sql);
     //6.处理结果
     System.out.println(count);
 &#125; catch (ClassNotFoundException e) &#123;
     e.printStackTrace();
 &#125; catch (SQLException e) &#123;
     e.printStackTrace();
 &#125; finally &#123;
     //7.释放资源
     if (stmt != null) &#123;
         try &#123;
             stmt.close();
         &#125; catch (SQLException e) &#123;
             e.printStackTrace();
         &#125;
     &#125;
     if (conn != null) &#123;
         try &#123;
             conn.close();
         &#125; catch (SQLException e) &#123;
             e.printStackTrace();
         &#125;
     &#125;
 &#125;
</code></pre> }<br>}<pre><code> - * 需求：不想传递参数（麻烦），还得保证工具类的通用性。
 - * 解决：配置文件
</code></pre></li>
</ul>
<ul>
<li><ol>
<li><p>ResultSet：结果集对象,封装查询结果</p>
<ul>
<li><ul>
<li>boolean next()：游标向下移动一行，判断当前行是否是最后一行末尾(是否有数据)；如果是，则返回false，如果不是则返回true。</li>
</ul>
</li>
<li><ul>
<li>getXxx(参数):获取数据</li>
</ul>
</li>
<li><ul>
<li>Xxx：代表数据类型   如：int getInt()、String getString()</li>
</ul>
</li>
<li><ul>
<li><p>参数：</p>
<ul>
<li><ol>
<li>int：代表列的编号，从1开始，如： getString(1)</li>
</ol>
</li>
<li><ol>
<li>String：代表列名称，如： getDouble(“balance”)</li>
</ol>
</li>
</ul>
</li>
</ul>
</li>
<li><ul>
<li><p>注意：</p>
<ul>
<li><ul>
<li><p>使用步骤：</p>
<ul>
<li><ol>
<li>游标向下移动一行</li>
</ol>
</li>
<li><ol>
<li>判断是否有数据</li>
</ol>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li><p>代码</p>
</li>
<li>/**</li>
</ul>
</li>
</ol>
<ul>
<li><p>执行DDL语句<br>*/<br>public class JDBCDemo6 {<br>public static void main(String[] args) {</p>
<pre><code>Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try &#123;
    //1. 注册驱动
    Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
    //2.获取连接对象
    conn = DriverManager.getConnection(&quot;jdbc:mysql:///db3&quot;, &quot;root&quot;, &quot;root&quot;);
    //3.定义sql
    String sql = &quot;select * from account&quot;;
    //4.获取执行sql对象
    stmt = conn.createStatement();
    //5.执行sql
    rs = stmt.executeQuery(sql);
    //6.处理结果
    //6.1 让游标向下移动一行
    rs.next();
    //6.2 获取数据
    int id = rs.getInt(1);
    String name = rs.getString(&quot;name&quot;);
    double balance = rs.getDouble(3);
    System.out.println(id + &quot;---&quot; + name + &quot;---&quot; + balance);
&#125; catch (ClassNotFoundException e) &#123;
    e.printStackTrace();
&#125; catch (SQLException e) &#123;
    e.printStackTrace();
&#125; finally &#123;
    //7.释放资源
    if (rs != null) &#123;
        try &#123;
            rs.close();
        &#125; catch (SQLException e) &#123;
            e.printStackTrace();
        &#125;
    &#125;
    if (stmt != null) &#123;
        try &#123;
            stmt.close();
        &#125; catch (SQLException e) &#123;
            e.printStackTrace();
        &#125;
    &#125;
    if (conn != null) &#123;
        try &#123;
            conn.close();
        &#125; catch (SQLException e) &#123;
            e.printStackTrace();
        &#125;
    &#125;
&#125;
</code></pre><p>}<br>}</p>
<pre><code>- /**
</code></pre></li>
<li><p>封装Emp表数据的JavaBean<br>*/<br>public class Emp {<br>private int id;<br>private String ename;<br>private int job_id;<br>private int mgr;<br>private Date joindate;<br>private double salary;<br>private double bonus;<br>private int dept_id;</p>
<p>public int getId() {</p>
<pre><code>return id;
</code></pre><p>}</p>
<p>public void setId(int id) {</p>
<pre><code>this.id = id;
</code></pre><p>}</p>
<p>public String getEname() {</p>
<pre><code>return ename;
</code></pre><p>}</p>
<p>public void setEname(String ename) {</p>
<pre><code>this.ename = ename;
</code></pre><p>}</p>
<p>public int getJob_id() {</p>
<pre><code>return job_id;
</code></pre><p>}</p>
<p>public void setJob_id(int job_id) {</p>
<pre><code>this.job_id = job_id;
</code></pre><p>}</p>
<p>public int getMgr() {</p>
<pre><code>return mgr;
</code></pre><p>}</p>
<p>public void setMgr(int mgr) {</p>
<pre><code>this.mgr = mgr;
</code></pre><p>}</p>
<p>public Date getJoindate() {</p>
<pre><code>return joindate;
</code></pre><p>}</p>
<p>public void setJoindate(Date joindate) {</p>
<pre><code>this.joindate = joindate;
</code></pre><p>}</p>
<p>public double getSalary() {</p>
<pre><code>return salary;
</code></pre><p>}</p>
<p>public void setSalary(double salary) {</p>
<pre><code>this.salary = salary;
</code></pre><p>}</p>
<p>public int getDept_id() {</p>
<pre><code>return dept_id;
</code></pre><p>}</p>
<p>public void setDept_id(int dept_id) {</p>
<pre><code>this.dept_id = dept_id;
</code></pre><p>}</p>
<p>public double getBonus() {</p>
<pre><code>return bonus;
</code></pre><p>}</p>
<p>public void setBonus(double bonus) {</p>
<pre><code>this.bonus = bonus;
</code></pre><p>}</p>
<p>@Override<br>public String toString() {</p>
<pre><code>return &quot;Emp&#123;&quot; +
        &quot;id=&quot; + id +
        &quot;, ename=&#39;&quot; + ename + &#39;\&#39;&#39; +
        &quot;, job_id=&quot; + job_id +
        &quot;, mgr=&quot; + mgr +
        &quot;, joindate=&quot; + joindate +
        &quot;, salary=&quot; + salary +
        &quot;, bonus=&quot; + bonus +
        &quot;, dept_id=&quot; + dept_id +
        &#39;&#125;&#39;;
</code></pre><p>}<br>}<br>JDBCDemo8.java<br>package cn.itcast.jdbc;</p>
</li>
</ul>
</li>
</ul>
<p>import cn.itcast.domain.Emp;<br>import cn.itcast.util.JDBCUtils;</p>
<p>import java.sql.*;<br>import java.util.ArrayList;<br>import java.util.List;</p>
<p>/**</p>
<ul>
<li><ul>
<li><p>定义一个方法，查询emp表的数据将其封装为对象，然后装载集合，返回。<br>*/<br>public class JDBCDemo8 {</p>
<p>public static void main(String[] args) {<br>   List<Emp> list = new JDBCDemo8().findAll();<br>   System.out.println(list);<br>   System.out.println(list.size());<br>   for (Emp x : list) {</p>
<pre><code>   System.out.println(x);
</code></pre><p>   }<br>}</p>
<p>/**</p>
<ul>
<li>查询所有emp对象<br>*</li>
<li>@return<br>*/<br>public List<Emp> findAll() {<br> Connection conn = null;<br> Statement stmt = null;<br> ResultSet rs = null;<br> List<Emp> list = null;<br> try {<pre><code> //1.注册驱动
 Class.forName(&quot;com.mysql.jdbc.Driver&quot;);
 //2.获取连接
 conn = DriverManager.getConnection(&quot;jdbc:mysql:///db3&quot;, &quot;root&quot;, &quot;root&quot;);
 //3.定义sql
 String sql = &quot;select * from emp&quot;;
 //4.获取执行sql的对象
 stmt = conn.createStatement();
 //5.执行sql
 rs = stmt.executeQuery(sql);
 //6.遍历结果集，封装对象，装载集合
 Emp emp = null;
 list = new ArrayList&lt;Emp&gt;();
 while (rs.next()) &#123;
     //获取数据
     int id = rs.getInt(&quot;id&quot;);
     String ename = rs.getString(&quot;ename&quot;);
     int job_id = rs.getInt(&quot;job_id&quot;);
     int mgr = rs.getInt(&quot;mgr&quot;);
     Date joindate = rs.getDate(&quot;joindate&quot;);
     double salary = rs.getDouble(&quot;salary&quot;);
     double bonus = rs.getDouble(&quot;bonus&quot;);
     int dept_id = rs.getInt(&quot;dept_id&quot;);
     // 创建emp对象,并赋值
     emp = new Emp();
     emp.setId(id);
     emp.setEname(ename);
     emp.setJob_id(job_id);
     emp.setMgr(mgr);
     emp.setJoindate(joindate);
     emp.setSalary(salary);
     emp.setBonus(bonus);
     emp.setDept_id(dept_id);
     //装载集合
     list.add(emp);
 &#125;
</code></pre> } catch (ClassNotFoundException e) {<pre><code> e.printStackTrace();
</code></pre> } catch (SQLException e) {<pre><code> e.printStackTrace();
</code></pre> } finally {<pre><code> if (rs != null) &#123;
     try &#123;
         rs.close();
     &#125; catch (SQLException e) &#123;
         e.printStackTrace();
     &#125;
 &#125;
 if (stmt != null) &#123;
     try &#123;
         stmt.close();
     &#125; catch (SQLException e) &#123;
         e.printStackTrace();
     &#125;
 &#125;
 if (conn != null) &#123;
     try &#123;
         conn.close();
     &#125; catch (SQLException e) &#123;
         e.printStackTrace();
     &#125;
 &#125;
</code></pre> }<br> return list;<br>}</li>
</ul>
</li>
</ul>
</li>
</ul>
<p>}</p>
<ul>
<li><p>抽取JDBC工具类 ： JDBCUtils</p>
<ul>
<li><ul>
<li>目的：简化书写</li>
</ul>
</li>
<li><ul>
<li><p>分析：</p>
<ul>
<li><ol>
<li>注册驱动也抽取</li>
</ol>
</li>
<li><ol>
<li><p>抽取一个方法获取连接对象</p>
<ul>
<li>jdbc.properties<br>url=…<br>user=…<br>password=…</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>抽取一个方法释放资源</p>
<ul>
<li><ul>
<li>代码实现：…</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li><p>JDBC练习_登录案例</p>
<ul>
<li><ul>
<li><p>需求：</p>
<ul>
<li><ol>
<li>通过键盘录入用户名和密码</li>
</ol>
</li>
<li><ol>
<li><p>判断用户是否登录成功</p>
<ul>
<li><ul>
<li>select * from user where username = “” and password = “”;</li>
</ul>
</li>
<li><ul>
<li>如果这个sql有查询结果，则成功，反之，则失败</li>
</ul>
</li>
<li><ul>
<li><p>步骤：</p>
<ul>
<li><ol>
<li><p>创建数据库表 user</p>
<ul>
<li>CREATE TABLE USER(<br>id INT PRIMARY KEY AUTO_INCREMENT,<br>username VARCHAR(32),<br>PASSWORD VARCHAR(32)<br>);</li>
</ul>
</li>
</ol>
</li>
</ul>
</li>
</ul>
</li>
</ul>
<p>INSERT INTO USER VALUES(NULL,’zhangsan’,’123’);<br>INSERT INTO USER VALUES(NULL,’lisi’,’234’);</p>
<ul>
<li><ol>
<li><p>代码实现：</p>
<ul>
<li>/**</li>
</ul>
</li>
</ol>
</li>
</ul>
</li>
</ol>
</li>
</ul>
</li>
<li>练习：</li>
<li><ul>
<li>需求：</li>
</ul>
</li>
<li><ol>
<li>通过键盘录入用户名和密码</li>
</ol>
</li>
<li><ol>
<li>判断用户是否登录成功<br>*/<br>public class JDBCDemo9 {</li>
</ol>
</li>
</ul>
<p>public static void main(String[] args) {<br>  //1.键盘录入，接受用户名和密码<br>  Scanner sc = new Scanner(System.in);<br>  System.out.println(“请输入用户名：”);<br>  String username = sc.nextLine();<br>  System.out.println(“请输入密码：”);<br>  String password = sc.nextLine();<br>  //2.调用方法 非静态方法，需要创建对象来调用<br>  boolean flag = new JDBCDemo9().login(username, password);<br>  //3.判断结果，输出不同语句<br>  if (flag) {</p>
<pre><code>  //登录成功
  System.out.println(&quot;登录成功！&quot;);
</code></pre><p>  } else {</p>
<pre><code>  System.out.println(&quot;用户名或密码错误！&quot;);
</code></pre><p>  }<br>}</p>
<p>/**</p>
<ul>
<li>登录方法<br>*/<br>public boolean login(String username, String password) {<br>if (username == null || password == null) {<pre><code>return false;
</code></pre>}<br>//连接数据库判断是否登录成功<br>Connection conn = null;<br>Statement stmt = null;<br>ResultSet rs = null;<br>//1.获取连接<br>try {<pre><code>conn = JDBCUtils.getConnection();
//2.定义sql
String sql = &quot;select * from user where username = &#39;&quot; + username + &quot;&#39; and password = &#39;&quot; + password + &quot;&#39; &quot;;
System.out.println(sql);
//3.获取执行sql的对象
stmt = conn.createStatement();
//4.执行查询
rs = stmt.executeQuery(sql);
//5.判断
</code></pre>   /* if(rs.next()){//如果有下一行，则返回true<pre><code>    return true;
&#125;else&#123;
    return false;
&#125;*/
return rs.next();//如果有下一行，则返回true
</code></pre>} catch (SQLException e) {<pre><code>e.printStackTrace();
</code></pre>} finally {<pre><code>JDBCUtils.close(rs, stmt, conn);
</code></pre>}<br>return false;<br>}</li>
</ul>
</li>
</ul>
</li>
</ul>
<p>}</p>
<h3 id="5-PreparedStatement：执行sql的对象"><a href="#5-PreparedStatement：执行sql的对象" class="headerlink" title="5. PreparedStatement：执行sql的对象"></a>5. PreparedStatement：执行sql的对象</h3><ul>
<li><ol>
<li><p>SQL注入问题：在拼接sql时，有一些sql的特殊关键字参与字符串的拼接，会造成安全性问题。</p>
<ul>
<li><ol>
<li>输入用户随便，输入密码：a’ or ‘a’ = ‘a</li>
</ol>
</li>
<li><ol>
<li>sql：select * from user where username = ‘fhdsjkf’ and password = ‘a’ or ‘a’ = ‘a’ </li>
</ol>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li>解决sql注入问题：使用PreparedStatement对象来解决</li>
</ol>
</li>
<li><ol>
<li>预编译的SQL：参数使用?作为占位符</li>
</ol>
</li>
<li><ol>
<li><p>步骤：</p>
<ul>
<li><ol>
<li>导入驱动jar包 mysql-connector-java-5.1.37-bin.jar</li>
</ol>
</li>
<li><ol>
<li>注册驱动</li>
</ol>
</li>
<li><ol>
<li>获取数据库连接对象 Connection</li>
</ol>
</li>
<li><ol>
<li><p>定义sql</p>
<ul>
<li><ul>
<li>注意：sql的参数使用？作为占位符， 如：select * from user where username = ? and password = ?;</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li>获取执行sql语句的对象 PreparedStatement  Connection.prepareStatement(String sql) </li>
</ol>
</li>
<li><ol>
<li><p>给？赋值：</p>
<ul>
<li><ul>
<li>方法： setXxx(参数1,参数2)</li>
</ul>
<ul>
<li><ul>
<li>参数1：？的位置编号 从1 开始</li>
</ul>
</li>
<li><ul>
<li>参数2：？的值</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li>执行sql，接受返回结果，不需要传递sql语句</li>
</ol>
</li>
<li><ol>
<li>处理结果</li>
</ol>
</li>
<li><ol>
<li>释放资源</li>
</ol>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>注意：后期都会使用PreparedStatement来完成增删改查的所有操作</p>
<ul>
<li><ol>
<li>可以防止SQL注入</li>
</ol>
</li>
<li><ol>
<li>效率更高</li>
</ol>
</li>
</ul>
</li>
</ol>
</li>
<li><p>登录案例——解决sql注入问题</p>
<ul>
<li><p>/**</p>
<ul>
<li>练习：</li>
<li><ul>
<li>需求：</li>
</ul>
</li>
<li><ol>
<li>通过键盘录入用户名和密码</li>
</ol>
</li>
<li><ol>
<li>判断用户是否登录成功<br>*/<br>public class JDBCDemo9 {</li>
</ol>
</li>
</ul>
<p>public static void main(String[] args) {<br>  //1.键盘录入，接受用户名和密码<br>  Scanner sc = new Scanner(System.in);<br>  System.out.println(“请输入用户名：”);<br>  String username = sc.nextLine();<br>  System.out.println(“请输入密码：”);<br>  String password = sc.nextLine();<br>  //2.调用方法 非静态方法，需要创建对象来调用<br>  boolean flag = new JDBCDemo9().login(username, password);<br>  //3.判断结果，输出不同语句<br>  if (flag) {</p>
<pre><code>  //登录成功
  System.out.println(&quot;登录成功！&quot;);
</code></pre><p>  } else {</p>
<pre><code>  System.out.println(&quot;用户名或密码错误！&quot;);
</code></pre><p>  }<br>}</p>
<p>/**</p>
<ul>
<li>登录方法<br>*/<br>public boolean login(String username, String password) {<br>if (username == null || password == null) {<pre><code>return false;
</code></pre>}<br>//连接数据库判断是否登录成功<br>Connection conn = null;<br>Statement stmt = null;<br>ResultSet rs = null;<br>//1.获取连接<br>try {<pre><code>conn = JDBCUtils.getConnection();
//2.定义sql
String sql = &quot;select * from user where username = &#39;&quot; + username + &quot;&#39; and password = &#39;&quot; + password + &quot;&#39; &quot;;
System.out.println(sql);
//3.获取执行sql的对象
stmt = conn.createStatement();
//4.执行查询
rs = stmt.executeQuery(sql);
//5.判断
</code></pre>   /* if(rs.next()){//如果有下一行，则返回true<pre><code>    return true;
&#125;else&#123;
    return false;
&#125;*/
return rs.next();//如果有下一行，则返回true
</code></pre>} catch (SQLException e) {<pre><code>e.printStackTrace();
</code></pre>} finally {<pre><code>JDBCUtils.close(rs, stmt, conn);
</code></pre>}<br>return false;<br>}</li>
</ul>
<p>/**</p>
<ul>
<li>登录方法,使用PreparedStatement实现<br>*/<br>public boolean login2(String username, String password) {<br>if (username == null || password == null) {<pre><code>return false;
</code></pre>}<br>//连接数据库判断是否登录成功<br>Connection conn = null;<br>PreparedStatement pstmt = null;<br>ResultSet rs = null;<br>//1.获取连接<br>try {<pre><code>conn = JDBCUtils.getConnection();
//2.定义sql
String sql = &quot;select * from user where username = ? and password = ?&quot;;
//3.获取执行sql的对象
pstmt = conn.prepareStatement(sql);
//给?赋值
pstmt.setString(1, username);
pstmt.setString(2, password);
//4.执行查询,不需要传递sql
rs = pstmt.executeQuery();
//5.判断
</code></pre>   /* if(rs.next()){//如果有下一行，则返回true<pre><code>    return true;
&#125;else&#123;
    return false;
&#125;*/
return rs.next();//如果有下一行，则返回true
</code></pre>} catch (SQLException e) {<pre><code>e.printStackTrace();
</code></pre>} finally {<pre><code>JDBCUtils.close(rs, pstmt, conn);
</code></pre>}<br>return false;<br>}</li>
</ul>
</li>
</ul>
</li>
</ul>
<h3 id="6-JDBC事务管理"><a href="#6-JDBC事务管理" class="headerlink" title="6 JDBC事务管理"></a>6 JDBC事务管理</h3><ul>
<li><ol>
<li>事务：一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败。</li>
</ol>
</li>
<li><ol>
<li><p>操作：</p>
<ul>
<li><ol>
<li>开启事务</li>
</ol>
</li>
<li><ol>
<li>提交事务</li>
</ol>
</li>
<li><ol>
<li>回滚事务</li>
</ol>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>使用Connection对象来管理事务</p>
<ul>
<li><ul>
<li><p>开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务</p>
<ul>
<li><ul>
<li>在执行sql之前开启事务</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li><ul>
<li><p>提交事务：commit() </p>
<ul>
<li><ul>
<li>当所有sql都执行完提交事务</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li><ul>
<li><p>回滚事务：rollback() </p>
<ul>
<li><ul>
<li>在catch中回滚事务</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>代码：</p>
<ul>
<li>/**</li>
</ul>
</li>
</ol>
<ul>
<li><p>事务操作——JDBC控制事务<br>*/<br>public class JDBCDemo10 {</p>
<p>public static void main(String[] args) {</p>
<pre><code>Connection conn = null;
PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
try &#123;
    //1.获取连接
    conn = JDBCUtils.getConnection();
    //开启事务
    conn.setAutoCommit(false);
    //2.定义sql
    //2.1 张三 - 500
    String sql1 = &quot;update account set balance = balance - ? where id = ?&quot;;
    //2.2 李四 + 500
    String sql2 = &quot;update account set balance = balance + ? where id = ?&quot;;
    //3.获取执行sql对象
    pstmt1 = conn.prepareStatement(sql1);
    pstmt2 = conn.prepareStatement(sql2);
    //4. 设置参数
    pstmt1.setDouble(1, 500);
    pstmt1.setInt(2, 1);
    pstmt2.setDouble(1, 500);
    pstmt2.setInt(2, 2);
    //5.执行sql
    pstmt1.executeUpdate();
    // 手动制造异常
    int i = 3 / 0;
    pstmt2.executeUpdate();
    //提交事务
    conn.commit();
&#125; catch (Exception e) &#123; // 只要出现异常，就进行事务的回滚 捕捉大异常
    //事务回滚
    try &#123;
        if (conn != null) &#123;
            conn.rollback();
        &#125;
    &#125; catch (SQLException e1) &#123;
        e1.printStackTrace();
    &#125;
    e.printStackTrace();
&#125; finally &#123;
    JDBCUtils.close(pstmt1, conn);
    JDBCUtils.close(pstmt2, null);
&#125;
</code></pre><p>}</p>
</li>
</ul>
</li>
</ul>
<p>}</p>
<h3 id="08-数据库连接池"><a href="#08-数据库连接池" class="headerlink" title="08 数据库连接池"></a>08 数据库连接池</h3><ul>
<li><ol>
<li><p>概念：</p>
<ul>
<li>其实就是一个容器(集合)，存放数据库连接的容器。当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象归还给容器。</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>好处：</p>
<ul>
<li><ol>
<li>节约资源</li>
</ol>
</li>
<li><ol>
<li>用户访问高效</li>
</ol>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>实现：</p>
<ul>
<li><ol>
<li><p>标准接口：DataSource   javax.sql包下的</p>
<ul>
<li><ol>
<li>方法：</li>
</ol>
<ul>
<li><ul>
<li>获取连接：getConnection()</li>
</ul>
</li>
<li><ul>
<li>归还连接：Connection.close()  如果连接对象Connection是从连接池中获取的，那么调用Connection.close()方法，则不会再关闭连接了，而是归还连接。</li>
</ul>
</li>
</ul>
</li>
<li><ol>
<li>一般我们不去实现它，有数据库厂商来实现</li>
</ol>
<ul>
<li><ol>
<li>C3P0：数据库连接池技术</li>
</ol>
</li>
<li><ol>
<li>Druid：数据库连接池实现技术，由阿里巴巴提供的</li>
</ol>
</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>C3P0：数据库连接池技术</p>
<ul>
<li><ul>
<li><p>步骤：</p>
<ul>
<li><ol>
<li><p>导入jar包 (两个) c3p0-0.9.5.2.jar、mchange-commons-java-0.2.12.jar ，</p>
<ul>
<li><ul>
<li>不要忘记导入数据库驱动jar包</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>定义配置文件：</p>
<ul>
<li><ul>
<li>名称： c3p0.properties 或者 c3p0-config.xml</li>
</ul>
</li>
<li><ul>
<li>路径：直接将文件放在src目录下即可。</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li>创建核心对象 数据库连接池对象 ComboPooledDataSource</li>
</ol>
</li>
<li><ol>
<li>获取连接： getConnection</li>
</ol>
</li>
</ul>
</li>
</ul>
</li>
<li><ul>
<li><p>代码：</p>
<ul>
<li>//1.创建数据库连接池对象<br>DataSource ds  = new ComboPooledDataSource();<br>//2. 获取连接对象<br>Connection conn = ds.getConnection();</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>Druid：数据库连接池实现技术，由阿里巴巴提供的。</p>
<ul>
<li><ol>
<li><p>步骤：</p>
<ul>
<li><ol>
<li>导入jar包 druid-1.0.9.jar</li>
</ol>
</li>
<li><ol>
<li>定义配置文件：</li>
</ol>
<ul>
<li><ul>
<li>是properties形式的</li>
</ul>
</li>
<li><ul>
<li>可以叫任意名称，可以放在任意目录下</li>
</ul>
</li>
</ul>
</li>
<li><ol>
<li>加载配置文件。Properties</li>
</ol>
</li>
<li><ol>
<li>获取数据库连接池对象：通过工厂来来获取  DruidDataSourceFactory</li>
</ol>
</li>
<li><ol>
<li>获取连接：getConnection</li>
</ol>
</li>
</ul>
</li>
</ol>
</li>
<li><ul>
<li><p>代码：</p>
<ul>
<li>/**</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ol>
<ul>
<li><p>Druid演示<br>*/<br>public class DruidDemo {<br>public static void main(String[] args) throws Exception {</p>
<pre><code>//1.导入jar包
//2.定义配置文件
//3.加载配置文件
Properties pro = new Properties();
InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream(&quot;druid.properties&quot;);
pro.load(is);
//4.获取连接池对象
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
//5.获取连接
Connection conn = ds.getConnection();
System.out.println(conn);
</code></pre><p>}<br>}</p>
<ul>
<li>/**</li>
</ul>
</li>
<li><p>Druid连接池的工具类<br>*/<br>public class JDBCUtils {</p>
<p>//1.定义成员变量 DataSource<br>private static DataSource ds ;</p>
<p>static{</p>
<pre><code>try &#123;
    //1.加载配置文件
    Properties pro = new Properties();
    pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream(&quot;druid.properties&quot;));
    //2.获取DataSource
    ds = DruidDataSourceFactory.createDataSource(pro);
&#125; catch (IOException e) &#123;
    e.printStackTrace();
&#125; catch (Exception e) &#123;
    e.printStackTrace();
&#125;
</code></pre><p>}</p>
<p>/**</p>
<ul>
<li>获取连接<br>*/<br>public static Connection getConnection() throws SQLException {<br> return ds.getConnection();<br>}</li>
</ul>
<p>/**</p>
<ul>
<li>释放资源<br><em>/<br>public static void close(Statement stmt,Connection conn){<br>/</em> if (stmt != null) {<pre><code> try &#123;
     stmt.close();
 &#125; catch (SQLException e) &#123;
     e.printStackTrace();
 &#125;
</code></pre> }<br> if (conn != null) {<pre><code> try &#123;
     conn.close();//归还连接
 &#125; catch (SQLException e) &#123;
     e.printStackTrace();
 &#125;
</code></pre> }*/<br>close(null,stmt,conn);<br>}</li>
</ul>
</li>
</ul>
</li>
</ul>
<pre><code>public static void close(ResultSet rs , Statement stmt, Connection conn)&#123;
    if(rs != null)&#123;
        try &#123;
            rs.close();
        &#125; catch (SQLException e) &#123;
            e.printStackTrace();
        &#125;
    &#125;
    if(stmt != null)&#123;
        try &#123;
            stmt.close();
        &#125; catch (SQLException e) &#123;
            e.printStackTrace();
        &#125;
    &#125;
    if(conn != null)&#123;
        try &#123;
            conn.close();//归还连接
        &#125; catch (SQLException e) &#123;
            e.printStackTrace();
        &#125;
    &#125;
&#125;

/**
 * 获取连接池方法
 */
public static DataSource getDataSource()&#123;
    return  ds;
&#125;
</code></pre><p>}<br>DruidDemo2.java<br>package cn.itcast.datasource.druid;</p>
<p>import cn.itcast.utils.JDBCUtils;</p>
<p>import java.sql.Connection;<br>import java.sql.PreparedStatement;<br>import java.sql.ResultSet;<br>import java.sql.SQLException;</p>
<p>/**</p>
<ul>
<li><p>使用新的工具类<br>*/<br>public class DruidDemo2 {</p>
<p> public static void main(String[] args) {</p>
</li>
</ul>
<p> //1.获取连接<br>            conn = JDBCUtils.getConnection();<br>            //2.定义sql<br>            String sql = “insert into account values(null, ?, ?)”; // 主键自增长<br>            //3.获取pstmt对象<br>            pstmt = conn.prepareStatement(sql);<br>            //4.给？赋值<br>            pstmt.setString(1, “王五”);<br>            pstmt.setDouble(2, 3001);<br>            //5.执行sql<br>            int count = pstmt.executeUpdate();<br>            System.out.println(count);<br>        } catch (SQLException e) {<br>            e.printStackTrace();<br>        } finally {<br>            //6. 释放资源<br>            JDBCUtils.close(pstmt, conn);<br>        }<br>    }</p>
<h3 id="09-JDBCTemplate"><a href="#09-JDBCTemplate" class="headerlink" title="09 JDBCTemplate"></a>09 JDBCTemplate</h3><ul>
<li><p>JDBCTemplate_介绍</p>
<ul>
<li><ul>
<li>Spring框架对JDBC的简单封装，提供了一个JDBCTemplate对象简化JDBC的开发。</li>
</ul>
</li>
</ul>
</li>
<li><ul>
<li><p>步骤：</p>
<ul>
<li><ol>
<li>导入jar包</li>
</ol>
</li>
<li><ol>
<li><p>创建JdbcTemplate对象（依赖于数据源DataSource）</p>
<ul>
<li><ul>
<li>JdbcTemplate template = new JdbcTemplate(ds);</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>调用JdbcTemplate的方法来完成CRUD的操作</p>
<ul>
<li><ul>
<li>update()：执行DML语句（增、删、改语句）。</li>
</ul>
</li>
<li><ul>
<li>queryForMap()：查询结果将结果集封装为map集合。</li>
</ul>
</li>
<li><ul>
<li>queryForList()：查询结果将结果集封装为list集合。</li>
</ul>
</li>
<li><ul>
<li>query()：查询结果，将结果封装为JavaBean对象。</li>
</ul>
</li>
<li><ul>
<li>queryForObject：查询结果，将结果封装为对象。</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>练习：</p>
<ul>
<li><ul>
<li><p>需求：</p>
<ul>
<li><ol>
<li>修改1号数据的 salary 为 10000</li>
</ol>
</li>
<li><ol>
<li>添加一条记录</li>
</ol>
</li>
<li><ol>
<li>删除刚才添加的记录</li>
</ol>
</li>
<li><ol>
<li>查询id为1的记录，将其封装为Map集合</li>
</ol>
</li>
<li><ol>
<li>查询所有记录，将其封装为List</li>
</ol>
</li>
<li><ol>
<li>查询所有记录，将其封装为Emp对象的List集合</li>
</ol>
</li>
<li><ol>
<li>查询总记录数</li>
</ol>
</li>
</ul>
</li>
</ul>
</li>
<li><ul>
<li><p>代码：</p>
<ul>
<li><ol>
<li><p>修改1号数据的 salary 为 10000</p>
<ul>
<li>子主题 1</li>
<li>子主题 2</li>
</ul>
</li>
</ol>
</li>
<li><ol>
<li><p>添加一条记录</p>
<ul>
<li>子主题 1</li>
</ul>
</li>
</ol>
</li>
<li><p>3.删除刚才添加的记录</p>
<ul>
<li>子主题 1</li>
</ul>
</li>
<li><p>JUnit 代码</p>
<ul>
<li>public class JdbcTemplateDemo2 {</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
</ol>
</li>
</ul>
<p>//Junit单元测试，可以让方法独立执行</p>
<p>//1. 获取JDBCTemplate对象<br>private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());</p>
<p>/**</p>
<ul>
<li>Junit单元测试，可以让方法独立执行<br>*/<br>@Test<br>public void test() {<br> System.out.println(“我被执行了…”);<br> int i = 3 / 0;<br> System.out.println(“我被执行了…”);<br>}</li>
</ul>
<p>/**</p>
<ul>
<li><ol>
<li>修改1号数据的 salary 为 10000<br>*/<br>@Test<br>public void test1() {<br>//2. 定义sql<br>String sql = “update emp set salary = 10000 where id = 1001”;<br>//3. 执行sql<br>int count = template.update(sql);<br>System.out.println(count);<br>}</li>
</ol>
</li>
</ul>
<p>/**</p>
<ul>
<li><ol>
<li>添加一条记录<br>*/<br>@Test<br>public void test2() {<br>String sql = “insert into emp(id,ename,dept_id) values(?,?,?)”;<br>int count = template.update(sql, 1015, “郭靖”, 10);<br>System.out.println(count);<br>}</li>
</ol>
</li>
</ul>
<p>/**</p>
<ul>
<li>3.删除刚才添加的记录<br>*/<br>@Test<br>public void test3() {<br> String sql = “delete from emp where id = ?”;<br> int count = template.update(sql, 1015);<br> System.out.println(count);<br>}</li>
</ul>
<p>/**</p>
<ul>
<li>4.查询id为1001的记录，将其封装为Map集合</li>
<li>注意：这个方法查询的结果集长度只能是1<br><em>/<br>@Test<br>public void test4() {<br> String sql = “select </em> from emp where id = ? or id = ?”;<br> Map<String, Object> map = template.queryForMap(sql, 1001);<br> // Map<String, Object> map = template.queryForMap(sql, 1001, 1002); // 错误<br> System.out.println(map);<br> // {id=1001, ename=孙悟空, job_id=4, mgr=1004, joindate=2000-12-17, salary=10000.00, bonus=null, dept_id=20}<br>}</li>
</ul>
<p>/**</p>
<ul>
<li><ol>
<li>查询所有记录，将其封装为List<br><em>/<br>@Test<br>public void test5() {<br>String sql = “select </em> from emp”;<br>List<Map<String, Object>&gt; list = template.queryForList(sql);<br>for (Map<String, Object> stringObjectMap : list) {<br>  System.out.println(stringObjectMap);<br>}<br>}</li>
</ol>
</li>
</ul>
<p>/**</p>
<ul>
<li><ol>
<li>查询所有记录，将其封装为Emp对象的List集合<br><em>/<br>@Test<br>public void test6() {<br>String sql = “select </em> from emp”;<br>List<Emp> list = template.query(sql, new RowMapper<Emp>() {<br>  @Override<br>  public Emp mapRow(ResultSet rs, int i) throws SQLException {<pre><code>  Emp emp = new Emp();
  int id = rs.getInt(&quot;id&quot;);
  String ename = rs.getString(&quot;ename&quot;);
  int job_id = rs.getInt(&quot;job_id&quot;);
  int mgr = rs.getInt(&quot;mgr&quot;);
  Date joindate = rs.getDate(&quot;joindate&quot;);
  double salary = rs.getDouble(&quot;salary&quot;);
  double bonus = rs.getDouble(&quot;bonus&quot;);
  int dept_id = rs.getInt(&quot;dept_id&quot;);
  emp.setId(id);
  emp.setEname(ename);
  emp.setJob_id(job_id);
  emp.setMgr(mgr);
  emp.setJoindate(joindate);
  emp.setSalary(salary);
  emp.setBonus(bonus);
  emp.setDept_id(dept_id);
  return emp;
</code></pre>  }<br>});<br>for (Emp emp : list) {<br>  System.out.println(emp);<br>}<br>}</li>
</ol>
</li>
</ul>
<p>/**</p>
<ul>
<li><ol>
<li>查询所有记录，将其封装为Emp对象的List集合<br><em>/<br>@Test<br>public void test6_2() {<br>String sql = “select </em> from emp”;<br>List<Emp> list = template.query(sql, new BeanPropertyRowMapper<Emp>(Emp.class));<br>for (Emp emp : list) {<br>  System.out.println(emp);<br>}<br>}</li>
</ol>
</li>
</ul>
<p>/**</p>
<ul>
<li><ol>
<li>查询总记录数<br>*/<br>@Test<br>public void test7() {<br>String sql = “select count(id) from emp”;<br>Long total = template.queryForObject(sql, Long.class);<br>System.out.println(total);<br>}</li>
</ol>
</li>
</ul>
</li>
</ul>
</li>
</ul>
<p>}</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://goofyer.gitee.io/notes-on-computer-expertise/2021/12/20/Java/%E7%AC%AC%E5%8D%81%E5%9B%9B%E7%AB%A0JDBC/" data-id="cl403sxre001yf8vu0lub8z4c" data-title="" class="article-share-link">Share</a>
      
      
      
    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/notes-on-computer-expertise/2021/12/20/Java/%E7%AC%AC%E5%8D%81%E4%BA%94%E7%AB%A0JavaWeb/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Newer</strong>
      <div class="article-nav-title">
        
          (no title)
        
      </div>
    </a>
  
  
    <a href="/notes-on-computer-expertise/2021/12/20/Java/%E7%AC%AC%E5%8D%81%E4%B8%89%E7%AB%A0MySQL/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Older</strong>
      <div class="article-nav-title"></div>
    </a>
  
</nav>

  
</article>


</section>
        
          <aside id="sidebar">
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Categories</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/C-%E5%B7%A5%E5%85%B7/">C#工具</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/C-%E5%B7%A5%E5%85%B7/">C++工具</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/Mysql/">Mysql</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/blender/">blender</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/linux/">linux</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/manim/">manim</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/spring/">spring</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/vba%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/">vba基础操作</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E5%88%9B%E9%80%A0%E6%A8%A1%E5%BC%8F/">创造模式</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F/">操作系统</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/">操作系统基础知识</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F%E5%BC%80%E5%8F%91/">操作系统开发</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%95%B0%E5%AD%A6/">数学</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84/">数据结构</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/">数据结构基础知识</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%A8%A1%E6%9D%BF/">模板</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%B7%B1%E5%BA%A6%E5%AD%A6%E4%B9%A0/">深度学习</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E8%8B%B1%E8%AF%AD/">英语</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E8%99%9A%E5%B9%BB4/">虚幻4</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BB%84%E6%88%90%E5%8E%9F%E7%90%86/">计算机组成原理</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C/">计算机网络</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tags</h3>
    <div class="widget">
      <ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="/notes-on-computer-expertise/tags/C/" rel="tag">C++</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tag Cloud</h3>
    <div class="widget tagcloud">
      <a href="/notes-on-computer-expertise/tags/C/" style="font-size: 10px;">C++</a>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Archives</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/notes-on-computer-expertise/archives/2022/05/">May 2022</a></li><li class="archive-list-item"><a class="archive-list-link" href="/notes-on-computer-expertise/archives/2022/03/">March 2022</a></li><li class="archive-list-item"><a class="archive-list-link" href="/notes-on-computer-expertise/archives/2022/02/">February 2022</a></li><li class="archive-list-item"><a class="archive-list-link" href="/notes-on-computer-expertise/archives/2022/01/">January 2022</a></li><li class="archive-list-item"><a class="archive-list-link" href="/notes-on-computer-expertise/archives/2021/12/">December 2021</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Recent Posts</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/notes-on-computer-expertise/2022/05/31/vue/%E5%89%8D%E7%AB%AF%E6%A1%86%E6%9E%B6%E9%AA%8C%E8%AF%81%E7%A0%81%E6%97%A0%E6%B3%95%E6%98%BE%E7%A4%BA/">(no title)</a>
          </li>
        
          <li>
            <a href="/notes-on-computer-expertise/2022/05/26/%E6%95%B0%E6%8D%AE%E5%BA%93/navicat%E5%AE%89%E8%A3%85/">(no title)</a>
          </li>
        
          <li>
            <a href="/notes-on-computer-expertise/2022/05/25/%E8%99%9A%E5%B9%BB4%E5%BC%95%E6%93%8E%E5%BC%80%E5%8F%91/%E8%99%9A%E5%B9%BB%E5%9B%9B%20mod%E5%88%B6%E4%BD%9C/">(no title)</a>
          </li>
        
          <li>
            <a href="/notes-on-computer-expertise/2022/05/23/python/python%E6%89%B9%E9%87%8F%E7%94%9F%E6%88%90%E6%95%B0%E6%8D%AE/">(no title)</a>
          </li>
        
          <li>
            <a href="/notes-on-computer-expertise/2022/05/23/vba%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C%E7%AC%94%E8%AE%B0/EXCEL%E5%9F%BA%E7%A1%80%E6%95%99%E7%A8%8B/">(no title)</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      
      &copy; 2022 John Doe<br>
      Powered by <a href="https://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>

    </div>
    <nav id="mobile-nav">
  
    <a href="/notes-on-computer-expertise/" class="mobile-nav-link">Home</a>
  
    <a href="/notes-on-computer-expertise/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    


<script src="/notes-on-computer-expertise/js/jquery-3.4.1.min.js"></script>



  
<script src="/notes-on-computer-expertise/fancybox/jquery.fancybox.min.js"></script>




<script src="/notes-on-computer-expertise/js/script.js"></script>





  </div>
<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
        tex2jax: {
            inlineMath: [ ["$","$"], ["\\(","\\)"] ],
            skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code'],
            processEscapes: true
        }
    });
    MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax();
        for (var i = 0; i < all.length; ++i)
            all[i].SourceElement().parentNode.className += ' has-jax';
    });
</script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js?config=TeX-MML-AM_CHTML"></script>
</body>
</html>